﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;

namespace WTest
{
    public class MySqlHelper
    {
        public string siteName { get; set; }
        
        public MySqlHelper(string _sitename)
        {
            siteName = _sitename;
        }

        public static MySqlConnection getmysqlcon(string ip, string Uname, string password, string DateBase)
        {
            string M_str_sqlcon = "server=" + ip + ";user id=" + Uname + ";password=" + password + ";database=" + DateBase; //根据自己的设置
            MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
            return myCon;
        }

        public MySqlConnection getmysqlcon()
        {
            string M_str_sqlcon = "server=localhost;user id=root;password=root;database=ppvod;charset=utf8"; //根据自己的设置
            if (siteName.ToLower() == "test")
                M_str_sqlcon = "server=localhost;user id=root;password=root;database=ppvod;charset=utf8";
            if (siteName.ToLower() == "nba8")
                M_str_sqlcon = "server=www.nba8.in;user id=nba8;password=a179113;database=nba8;charset=utf8";

            if (siteName.ToLower() == "m5917")
                M_str_sqlcon = "server=173.0.51.170;userid=m5917;password=qq19791109;database=m5917;charset=utf8"; //888
            if (siteName.ToLower() == "5917le")
                M_str_sqlcon = "server=173.0.51.170;userid=5917le;password=qq19791109;database=5917le;charset=utf8"; //888
            if (siteName.ToLower() == "77oe")
                M_str_sqlcon = "server=173.0.51.170;userid=77oe;password=qq19791109;database=77oe;charset=utf8"; //888
            
            MySqlConnection myCon = new MySqlConnection(M_str_sqlcon);
            return myCon;
        }

        public static void PrepareCommand(MySqlCommand cmd, MySqlConnection con, CommandType cmdType, string cmdText, MySqlParameter[] parames)
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
            cmd.Connection = con;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;

            if (parames != null)
            {
                cmd.Parameters.AddRange(parames);
            }
        }

        public object ExecuteScale(CommandType cmdType, string cmdText, params MySqlParameter[] parames)
        {
            MySqlConnection mysqlcon = getmysqlcon();
            MySqlCommand mysqlcom = new MySqlCommand();
            try
            {
                PrepareCommand(mysqlcom, mysqlcon, cmdType, cmdText, parames);
                object result = mysqlcom.ExecuteScalar();
                mysqlcom.Parameters.Clear();
                return result;
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                mysqlcom.Dispose();
                mysqlcon.Close();
            }
        }

        // 读取数据 datatable   
        public DataTable GetDataTable(string sSQL)
        {
            DataTable dt = null;

            MySqlConnection mysqlcon = getmysqlcon();
            MySqlCommand mysqlcom = new MySqlCommand();

            try
            {
                PrepareCommand(mysqlcom, mysqlcon, CommandType.Text, sSQL, null);
                MySqlDataAdapter adapter = new MySqlDataAdapter(mysqlcom);
                dt = new DataTable();
                adapter.Fill(dt);
                mysqlcon.Close();

            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                mysqlcom.Dispose();
                mysqlcon.Close();
            }

            return dt;
        }   

        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter[] parames)
        {
            MySqlConnection mysqlcon = getmysqlcon();
            MySqlCommand mysqlcom = new MySqlCommand();
            try
            {
                PrepareCommand(mysqlcom, mysqlcon, cmdType, cmdText, parames);
                int result = mysqlcom.ExecuteNonQuery();
                mysqlcom.Parameters.Clear();
                return result;
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                mysqlcom.Dispose();
                mysqlcon.Close();
            }
        }

    }
}
